use serde_json::Value;
use std::io::Cursor;
use umya_spreadsheet::{
    Alignment, Border, Cell, HorizontalAlignmentValues, Spreadsheet, Style,
    VerticalAlignmentValues, Worksheet,
};
use crate::prelude::{
    get_my_pool, get_page_config,
    report::{DisplayCols, SpecialRowData},
    table_props::TableProps,
    Align, Callback, ColumnCell, ColumnCells, Error, MysqlDatabase, OrderItems, Result,
    WhereCauses,
};
use super::index_to_alpha;
pub async fn export_xlsx(
    conf_name: &str,
    display_cols: DisplayCols,
    filters: Option<WhereCauses>,
    auth_filter: Option<&str>,
    orders: Option<OrderItems>,
    callback: Option<Box<dyn Callback>>,
) -> Result<Vec<u8>> {
    let conf = get_page_config(conf_name)?;
    let cols = &conf.field_props;
    let tx = get_my_pool().begin().await?;
    let mut mydb = MysqlDatabase::from_with_tx(conf_name, callback, tx)?;
    let (datas, _) = mydb
        .find_in_page(
            None,
            filters,
            auth_filter,
            orders,
            &display_cols,
            1,
            1000000,
        )
        .await?;
    let col_cells = match display_cols {
        DisplayCols::Default => {
            let show_clos = conf.get_show_columns();
            ColumnCells::from_columns(&cols, &show_clos)
        }
        DisplayCols::Part(items) => {
            let show_clos: Vec<&str> = items.iter().map(|i| i.as_str()).collect();
            ColumnCells::from_columns(&cols, &show_clos)
        }
    };
    let mut ew = ExcelWriter::new(col_cells)?;
    let sheet_name = "Sheet1";
    ew.create_header(sheet_name, false, None, None)?;
    ew.create_body(sheet_name, datas, vec![])?;
    let arr_buff = ew.to_bytes();
    mydb.commit().await?;
    arr_buff
}
pub struct ExcelWriter {
    book: Spreadsheet,
    col_cells: ColumnCells,
    body_start_line: usize,
}
impl ExcelWriter {
    pub fn new(
        col_cells: ColumnCells,
    ) -> Result<Self> {
        let book = umya_spreadsheet::new_file_empty_worksheet();
        Ok(Self {
            book,
            col_cells,
            body_start_line: 1,
        })
    }
    pub fn create_header(
        &mut self,
        sheet_name: &str,
        auto_width: bool,
        title: Option<&str>,
        sub_title: Option<&str>,
    ) -> Result<()> {
        let sheet = if let Some(sheet) = self.book.get_sheet_by_name_mut(sheet_name) {
            sheet
        } else {
            self.book.new_sheet(sheet_name).or_else(|err| {
                let err_msg = format!("创建sheet失败:{}", err);
                Err(Error::Other(err_msg))
            })?
        };
        let mut i = 1_u32;
        let mut j = 1_u32;
        let col_cells = self.col_cells.get_column_cells();
        let mut clen = col_cells.len() as u32;
        let leafs = self.col_cells.get_leaf_cells();
        let cells_count = leafs.len() as u32;
        if cells_count > 0 {
            if let Some(title) = title {
                let range = format!("A{}:{}{}", i, index_to_alpha(cells_count), i);
                sheet.add_merge_cells(range);
                sheet.get_cell_mut((&1, &i)).set_value_string(title);
                i += 1;
                clen += 1;
            }
            if let Some(sub_title) = sub_title {
                let range = format!("A{}:{}{}", i, index_to_alpha(cells_count), i);
                sheet.add_merge_cells(range);
                sheet.get_cell_mut((&1, &i)).set_value_string(sub_title);
                i += 1;
                clen += 1;
            }
        }
        for col in col_cells.iter() {
            for cell in col.iter() {
                match cell {
                    ColumnCell::Leaf(leaf) => {
                        if i < clen {
                            let rowspan = clen - i;
                            let range = format!(
                                "{}{}:{}{}",
                                index_to_alpha(j),
                                i,
                                index_to_alpha(j),
                                i + rowspan as u32,
                            );
                            sheet.add_merge_cells(range);
                        }
                        let title = leaf.title.as_ref().map(|t| t.as_str()).unwrap_or("");
                        sheet.get_cell_mut((&j, &i)).set_value_string(title);
                        j = j + 1;
                    }
                    ColumnCell::Branch(branch) => {
                        if branch.colspan > 1 || branch.rowspan > 1 {
                            let range = format!(
                                "{}{}:{}{}",
                                index_to_alpha(j),
                                i,
                                index_to_alpha(j - 1 + branch.colspan as u32),
                                i - 1 + branch.rowspan as u32
                            );
                            sheet.add_merge_cells(range);
                        }
                        sheet
                            .get_cell_mut((&j, &i))
                            .set_value(branch.title.as_str());
                        j = j + branch.colspan as u32;
                    }
                    ColumnCell::None => j = j + 1,
                }
            }
            i = i + 1;
            j = 1;
        }
        self.body_start_line = i as usize;
        leafs.iter().enumerate().for_each(|(j, leaf)| {
            if let Some(lps) = leaf.list_props.as_ref() {
                let col = index_to_alpha(j as u32 + 1);
                if lps.width > 200 {
                    sheet
                        .get_column_dimension_mut(&col)
                        .set_width(lps.width as f64 / 7.0);
                } else if auto_width {
                    sheet.get_column_dimension_mut(&col).set_auto_width(true);
                } else {
                    let width = if lps.width == 0 { 100 } else { lps.width };
                    sheet
                        .get_column_dimension_mut(&col)
                        .set_width(width as f64 / 7.0);
                };
            }
        });
        let mut column_start = 1_u32;
        if title.is_some() {
            set_style(
                sheet,
                "A1",
                HorizontalAlignmentValues::Center,
                true,
                None,
                "黑体",
                18.0,
                true,
            );
            column_start = 2;
        }
        let range = format!("A{column_start}:{}{}", index_to_alpha(cells_count), clen);
        set_style(
            sheet,
            &range,
            HorizontalAlignmentValues::Center,
            true,
            None,
            "微软雅黑",
            12.8,
            true,
        );
        Ok(())
    }
    pub fn create_body(&mut self, sheet_name: &str, datas: Vec<Value>,special_row_datas: Vec<(usize, SpecialRowData)>) -> Result<()> {
        let start_line = self.body_start_line;
        let sheet = if let Some(sheet) = self.book.get_sheet_by_name_mut(sheet_name) {
            sheet
        } else {
            self.book.new_sheet(sheet_name).or_else(|err| {
                let err_msg = format!("创建sheet失败:{}", err);
                Err(Error::Other(err_msg))
            })?
        };
        let mut row_datas: Vec<Vec<Value>> = Vec::new();
        for data in datas.into_iter() {
            match data {
                Value::Array(arr) => {
                    let row_data = arr
                        .into_iter()
                        .enumerate()
                        .filter_map(|(i, r)| {
                            if !self.col_cells.is_no_title(i) {
                                Some(r)
                            } else {
                                None
                            }
                        })
                        .collect();
                    row_datas.push(row_data);
                }
                Value::Object(mval) => {
                    let leafs = self.col_cells.get_leaf_cells();
                    let mut mval: Vec<(Value, usize)> = mval
                        .into_iter()
                        .map(|(k, v)| {
                            let idx = leafs.iter().position(|leaf| {
                                if let Some(lc_name) = leaf.dict_col_name.as_ref() {
                                    lc_name.as_str() == k
                                } else {
                                    leaf.name.as_str() == k
                                }
                            });
                            (v, idx)
                        })
                        .filter(|(_v, idx)| idx.is_some())
                        .map(|(v, idx)| (v, idx.unwrap()))
                        .collect();
                    mval.sort_by(|a, b| a.1.cmp(&b.1));
                    let row = mval.into_iter().map(|(v, _idx)| v).collect();
                    row_datas.push(row);
                }
                _ => return Err(Error::Other("Value必须是Array或者Object类型".to_string())),
            }
        }
        let count = row_datas.len() as u32;
        let mut line = start_line;
        let mut range_aligns: Vec<(String, Align)> = Vec::new();
        for (i, data) in row_datas.into_iter().enumerate() {
            for (j, val) in data.into_iter().enumerate() {
                let mut cell_cal = val;
                if let Some((_, sr)) = special_row_datas.iter().find(|(row, _)| *row == i) {
                    let start_idx = sr.col_range.start as u32 + 1;
                    let range = format!(
                        "{}{}:{}{}",
                        index_to_alpha(start_idx),
                        line,
                        index_to_alpha(sr.col_range.end as u32 + 1),
                        line
                    );
                    if sr.is_merge {
                        if sr.col_range.start == j as u8 {
                            sheet.add_merge_cells(&range);
                            cell_cal = sr.show_value.clone();
                        } else if sr.col_range.start > j as u8 && sr.col_range.end <= j as u8 {
                            continue;
                        }
                    } else if sr.show_value != Value::Null {
                        cell_cal = sr.show_value.clone();
                    }
                    if let Some(align) = sr.align {
                        range_aligns.push((range, align));
                    }
                }
                let cell = sheet.get_cell_mut((&(j as u32 + 1), &(line as u32)));
                set_cell_value(cell, cell_cal);
            }
            line = line + 1;
        }
        set_body_style(sheet, &self.col_cells, start_line, count);
        for (range, align) in range_aligns {
            set_range_alignment(sheet, &range, align);
        }
        Ok(())
    }
    pub fn save(&self, file_path: &str) -> Result<()> {
        let path = std::path::Path::new(file_path);
        umya_spreadsheet::writer::xlsx::write(&self.book, path)?;
        Ok(())
    }
    pub fn to_bytes(&self) -> Result<Vec<u8>> {
        let mut buff = Cursor::new(Vec::new());
        umya_spreadsheet::writer::xlsx::write_writer(&self.book, buff.get_mut()).or_else(
            |err| {
                let err_msg = format!("excel转bytes出错:{}", err);
                Err(Error::Other(err_msg))
            },
        )?;
        Ok(buff.into_inner())
    }
}
fn set_body_style(sheet: &mut Worksheet, col_cells: &ColumnCells, start_line: usize, count: u32) {
    if count == 0 {
        return;
    }
    let leafs = col_cells.get_leaf_cells();
    leafs.iter().enumerate().for_each(|(j, leaf)| {
        if let Some(lps) = leaf.list_props.as_ref() {
            let col = index_to_alpha(j as u32 + 1);
            let wrap_text = if lps.width > 200 { true } else { false };
            let range = format!(
                "{}{}:{}{}",
                col,
                start_line,
                col,
                count + start_line as u32 - 1
            );
            let h_align = if lps.align == Align::Center {
                HorizontalAlignmentValues::Center
            } else if lps.align == Align::Left {
                HorizontalAlignmentValues::Left
            } else {
                HorizontalAlignmentValues::Right
            };
            let fcode = get_format_code(lps);
            set_style(
                sheet,
                &range,
                h_align,
                wrap_text,
                fcode,
                "微软雅黑",
                12.8,
                false,
            );
        }
    });
}
fn set_range_alignment(sheet: &mut Worksheet, range: &str, align: Align) {
    let h_align = if align == Align::Center {
        HorizontalAlignmentValues::Center
    } else if align == Align::Left {
        HorizontalAlignmentValues::Left
    } else {
        HorizontalAlignmentValues::Right
    };
    set_style(sheet, range, h_align, false, None, "微软雅黑", 12.8, false);
}
fn set_style(
    sheet: &mut Worksheet,
    range: &str,
    h_align: HorizontalAlignmentValues,
    wrap_text: bool,
    format_code: Option<String>,
    font_name: &str,
    font_size: f64,
    bold: bool,
) {
    let mut sty = Style::default();
    let mut align = Alignment::default();
    align.set_horizontal(h_align);
    align.set_vertical(VerticalAlignmentValues::Center);
    align.set_wrap_text(wrap_text);
    sty.set_alignment(align);
    let font = sty.get_font_mut();
    font.get_font_name_mut().set_val(font_name);
    font.get_font_bold_mut().set_val(bold);
    font.get_font_size_mut().set_val(font_size);
    let border = sty.get_borders_mut();
    border.get_top_mut().set_border_style(Border::BORDER_THIN);
    border
        .get_bottom_mut()
        .set_border_style(Border::BORDER_THIN);
    border.get_left_mut().set_border_style(Border::BORDER_THIN);
    border.get_right_mut().set_border_style(Border::BORDER_THIN);
    if let Some(format_code) = format_code.as_deref() {
        sty.get_number_format_mut().set_format_code(format_code);
    }
    sheet.set_style_by_range(&range, sty);
}
fn set_cell_value(cell: &mut Cell, value: Value) {
    match value {
        Value::String(s) => cell.set_value_string(s),
        Value::Number(n) => {
            if n.is_i64() {
                let iv = n.as_i64().unwrap();
                cell.set_value_number(iv as f64)
            } else {
                cell.set_value_number(n.as_f64().unwrap())
            }
        }
        Value::Bool(b) => cell.set_value_bool(b),
        Value::Null => cell.set_value_string(""),
        _ => cell.set_value_string(""),
    };
}
fn get_format_code(tp: &TableProps) -> Option<String> {
    let prec_fn = |f: &str| {
        let p = f[1..2].parse::<u8>().unwrap_or(0);
        let pstr: Vec<&str> = (0..p).map(|_v| "0").collect();
        pstr.join("")
    };
    let format_code = if let Some(f) = tp.format.as_deref() {
        if f.ends_with("%") {
            Some(format!("0.{}%", prec_fn(f)))
        } else if f.ends_with("n") {
            Some(format!("#,##0.{}", prec_fn(f)))
        } else {
            None
        }
    } else {
        None
    };
    format_code
}
